package cn.iluwen.airline.Mapper;

import cn.iluwen.airline.Entities.Flight;
import org.apache.ibatis.annotations.*;

import java.time.LocalDateTime;
import java.util.List;

@Mapper
public interface FlightMapper {

    /**
     * 查询所有航班
     * @return 航班列表
     */
    @Select("SELECT * FROM flights")
    List<Flight> findAllFlights();
    
    /**
     * 分页查询航班
     * @return 航班列表
     */
    @Select("SELECT * FROM flights ORDER BY flight_id")
    List<Flight> findFlightsWithPaging();
    
    /**
     * 根据航班号查询航班
     * @param flightNumber 航班号
     * @return 航班列表
     */
    @Select("SELECT * FROM flights WHERE flight_number = #{flightNumber}")
    Flight findByFlightNumber(String flightNumber);
    
    /**
     * 根据航班号模糊查询航班
     * @param flightNumber 航班号关键词
     * @return 航班列表
     */
    @Select("SELECT * FROM flights WHERE flight_number LIKE CONCAT('%', #{flightNumber}, '%')")
    List<Flight> findByFlightNumberLike(@Param("flightNumber") String flightNumber);
    
    /**
     * 根据出发地和目的地查询航班
     * @param departureAirportId 出发机场ID
     * @param arrivalAirportId 到达机场ID
     * @return 航班列表
     */
    @Select("SELECT * FROM flights WHERE departure_airport_id = #{departureAirportId} AND arrival_airport_id = #{arrivalAirportId}")
    List<Flight> findByRoute(@Param("departureAirportId") String departureAirportId, @Param("arrivalAirportId") String arrivalAirportId);
    
    /**
     * 根据出发时间范围查询航班
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 航班列表
     */
    @Select("SELECT * FROM flights WHERE departure_time BETWEEN #{startTime} AND #{endTime}")
    List<Flight> findByDepartureTimeRange(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
    
    /**
     * 根据出发城市、到达城市和出发日期查询航班
     * @param departureCityAirportIds 出发城市的机场ID列表
     * @param arrivalCityAirportIds 到达城市的机场ID列表
     * @param startTime 当天起始时间
     * @param endTime 当天结束时间
     * @return 航班列表
     */
    @Select("<script>" +
            "SELECT f.* FROM flights f " +
            "WHERE f.departure_airport_id IN " +
            "<foreach collection='departureCityAirportIds' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            " AND f.arrival_airport_id IN " +
            "<foreach collection='arrivalCityAirportIds' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            " AND f.departure_time BETWEEN #{startTime} AND #{endTime}" +
            " AND f.status != 'cancelled'" +
            " ORDER BY f.departure_time" +
            "</script>")
    List<Flight> findByCitiesAndDate(
            @Param("departureCityAirportIds") List<String> departureCityAirportIds,
            @Param("arrivalCityAirportIds") List<String> arrivalCityAirportIds,
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime
    );
    
    /**
     * 根据ID查询航班
     * @param flightId 航班ID
     * @return 航班对象
     */
    @Select("SELECT * FROM flights WHERE flight_id = #{flightId}")
    Flight findById(String flightId);
    
    /**
     * 根据多个ID查询航班
     * @param flightIds 航班ID列表
     * @return 航班列表
     */
    @Select("<script>" +
            "SELECT * FROM flights WHERE flight_id IN " +
            "<foreach collection='list' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "</script>")
    List<Flight> findByIds(@Param("list") List<String> flightIds);
    
    /**
     * 添加航班
     * @param flight 航班对象
     * @return 影响的行数
     */
    @Insert("INSERT INTO flights (flight_id, flight_number, departure_airport_id, arrival_airport_id, " +
            "departure_time, arrival_time, economy_price, business_price, " +
            "total_economy_seats, total_business_seats, status) " +
            "VALUES (#{flightId}, #{flightNumber}, #{departureAirportId}, #{arrivalAirportId}, " +
            "#{departureTime}, #{arrivalTime}, #{economyPrice}, #{businessPrice}, " +
            "#{totalEconomySeats}, #{totalBusinessSeats}, #{status})")
    int insert(Flight flight);
    
    /**
     * 更新航班信息
     * @param flight 航班对象
     * @return 影响的行数
     */
    @Update("UPDATE flights SET flight_number = #{flightNumber}, departure_airport_id = #{departureAirportId}, " +
            "arrival_airport_id = #{arrivalAirportId}, departure_time = #{departureTime}, " +
            "arrival_time = #{arrivalTime}, economy_price = #{economyPrice}, " +
            "business_price = #{businessPrice}, total_economy_seats = #{totalEconomySeats}, " +
            "total_business_seats = #{totalBusinessSeats}, status = #{status} " +
            "WHERE flight_id = #{flightId}")
    int update(Flight flight);
    
    /**
     * 更新航班状态
     * @param flightId 航班ID
     * @param status 航班状态
     * @return 影响的行数
     */
    @Update("UPDATE flights SET status = #{status} WHERE flight_id = #{flightId}")
    int updateStatus(@Param("flightId") String flightId, @Param("status") String status);
    
    /**
     * 删除航班
     * @param flightId 航班ID
     * @return 影响的行数
     */
    @Delete("DELETE FROM flights WHERE flight_id = #{flightId}")
    int delete(String flightId);
} 